package com.zbj.data.jpa.repository;

import com.zbj.data.jpa.entity.Student;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Collection;
import java.util.Date;
import java.util.List;

/**
 * StudentRepository
 * https://docs.spring.io/spring-data/jpa/docs/1.11.6.RELEASE/reference/html/#repositories.create-instances.java-config
 * Table 4. Supported keywords inside method names
 * @author weigang
 * @create 2017-09-10
 **/
public interface StudentRepository extends JpaRepository<Student, Long>, JpaSpecificationExecutor<Student> {

	List<Student> findByName(String name);

	/**
	 *
	 … where x.name = ?1 and x.age = ?2
	 * @param name
	 * @param age
	 * @return
	 */
	List<Student> findByNameAndAge(String name, Integer age);

	/**
	 * … where x.name = ?1 or x.age = ?2
	 * @param name
	 * @param age
	 * @return
	 */
	List<Student> findByNameOrAge(String name, Integer age);

	/**
	 * … where x.age between ?1 and ?2
	 * @param start
	 * @param end
	 * @return
	 */
	List<Student> findByAgeBetween(Integer start, Integer end);

	/**
	 * … where x.age < ?1
	 * @param age
	 * @return
	 */
	List<Student> findByAgeLessThan(Integer age);

	/**
	 * … where x.age <= ?1
	 * @param age
	 * @return
	 */
	List<Student> findByAgeLessThanEqual(Integer age);

	/**
	 * … where x.age > ?1
	 * @param age
	 * @return
	 */
	List<Student> findByAgeGreaterThan(Integer age);

	/**
	 * … where x.age >= ?1
	 * @param age
	 * @return
	 */
	List<Student> findByAgeGreaterThanEqual(Integer age);

	/**
	 * … where x.createDate > ?1
	 * @return
	 */
	List<Student> findByCreateDateAfter(Date createDate);

	/**
	 * … where x.createDate < ?1
	 * @param createDate
	 * @return
	 */
	List<Student> findByCreateDateBefore(Date createDate);

	/**
	 * … where x.age is null
	 * @return
	 */
	List<Student> findByAgeIsNull();

	/**
	 * IsNotNull,NotNull
	 * … where x.age not null
	 * @return
	 */
	List<Student> findByAgeNotNull();

	/**
	 * … where x.name like ?1
	 * @param name
	 * @return
	 */
	List<Student> findByNameLike(String name);

	/**
	 * … where x.name not like ?1
	 * @param name
	 * @return
	 */
	List<Student> findByNameNotLike(String name);

	/**
	 * … where x.name like ?1 (parameter bound with appended %)
	 * @param name
	 * @return
	 */
	List<Student> findByNameStartingWith(String name);

	/**
	 * … where x.name like ?1 (parameter bound with prepended %)
	 * @param name
	 * @return
	 */
	List<Student> findByNameEndingWith(String name);

	/**
	 * … where x.firstname like ?1 (parameter bound wrapped in %)
	 * @param name
	 * @return
	 */
	List<Student> findByNameContaining(String name);

	/**
	 * … where x.name = ?1 order by x.age desc
	 * @param name
	 * @return
	 */
	List<Student> findByNameOrderByAgeDesc(String name);

	/**
	 * … where x.name <> ?1
	 * @param name
	 * @return
	 */
	List<Student> findByNameNot(String name);

	/**
	 * … where x.age in ?1
	 * @param ages
	 * @return
	 */
	List<Student> findByAgeIn(Collection<Integer> ages);

	/**
	 * … where x.age not in ?1
	 * @param ages
	 * @return
	 */
	List<Student> findByAgeNotIn(Collection<Integer> ages);

//	/**
//	 * … where x.active = true
//	 * @return
//	 */
//	List<Student> findByActiveTrue();
//
//	/**
//	 * … where x.active = false
//	 * @return
//	 */
//	List<Student> findByActiveFalse();

	/**
	 * … where UPPER(x.firstame) = UPPER(?1)
	 * @param name
	 * @return
	 */
	List<Student> findByNameIgnoreCase(String name);

	/**
	 * Query 简单查询
	 * @param startAge
	 * @param endAge
	 * @return
	 */
	@Query("select s from Student s where s.age >= ?1 and s.age <= ?2")
	List<Student> findByAgeRange(Integer startAge, Integer endAge);

	/**
	 * like 查询
	 * 使用@Param注解注入参数
	 * @param address
	 * @return
	 */
	@Query(value = "select s.name, s.age, s.address from Student s where s.address like %:address%")
	List<Student> findByAddressLike(@Param("address") String address);

	/**
	 * 本地查询 使用原生的sql语句（根据数据库的不同，在sql的语法或结构方面可能有所区别）进行查询数据库的操作
	 * @param name
	 * @return
	 */
	@Query(value = "select * from student s where s.name = ?1", nativeQuery = true)
	List<Student> findByNameNative(String name);

	/**
	 * SPEL表达式  '#{#entityName}'值为'Student'对象对应的数据库表名称
	 * 此处不能使用* (select * from)
	 * @param name
	 * @return
	 */
	@Query(value = "select s from #{#entityName} s where s.name = ?1")
	List<Student> findByNameQuery(String name);

	/**
	 * 使用namedQuery
	 * @param id
	 * @return
	 */
	@Query("select s from Student s where s.address = ?1")
	List<Student> findById(String id);

//	/**
//	 * 使用namedQuery
//	 * @param name
//	 * @return
//	 */
//	List<Student> findByNameNameQuery(String name);

	/**
	 * 使用namedQuery
	 * @param age
	 * @return
	 */
	List<Student> findByAddress(@Param("address") String address);
}
